﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class Categories : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;

    protected void Page_Load(object sender, EventArgs e)
    {
        FillCategory();
    }

    void InitializeAOObject()
    {
        con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        cmd = new SqlCommand();
        cmd.Connection = con;
    }

    void FillCategory()
    {
        InitializeAOObject();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetCategories";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable("Categories");
        da.Fill(dt);
        GridViewCategory.AutoGenerateSelectButton = true;
        GridViewCategory.AllowPaging = true;
        GridViewCategory.PageSize = 10;
        GridViewCategory.DataSource = dt;
        GridViewCategory.DataBind();
    }

    void InsertCategory()
    {
        try
        {
            InitializeAOObject();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_InsertCategories";
            
            SqlParameter para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCategoryName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@description", SqlDbType.NVarChar);
                para.Size = 200;
                para.Value = txtDescription.Text;
                cmd.Parameters.Add(para);

                cmd.ExecuteNonQuery();
                string script = "alert(\"Insert successfull!\");";
                ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);

        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    void UpdateCategory()
    {
        
        try
        {
            InitializeAOObject();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_UpdateCategories";

            SqlParameter para = new SqlParameter(@"categoryid", SqlDbType.Int);
            para.Value = txtCategoryID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = txtCategoryName.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@description", SqlDbType.NVarChar);
            para.Size = 200;
            para.Value = txtDescription.Text;
            cmd.Parameters.Add(para);

            cmd.ExecuteNonQuery();
            string script = "alert(\"Update successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                              "ServerControlScript", script, true);
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    void deleteCategory()
    {
        try
        {
            InitializeAOObject();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_DeleteCategories";

            SqlParameter para = new SqlParameter(@"categoryid",SqlDbType.Int);
            para.Value = txtCategoryID.Text;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();

            string script = "alert(\"Delete successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    void clear()
    {
        txtCategoryID.Text = "";
        txtCategoryName.Text = "";
        txtDescription.Text = "";
        txtSearch.Text = "";
    }

    void SearchByName()
    {
        
        try
        {
            InitializeAOObject();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_SearchCategoriesByNames";
            SqlParameter para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = txtSearch.Text.Trim();
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("Categories");
            da.Fill(dt);
            GridViewCategory.DataSource = dt;
            GridViewCategory.DataBind();
        }
        catch(Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;
        }
        finally
        {
            con.Close();
        }
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        InsertCategory();
        FillCategory();
    }


    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        UpdateCategory();
        FillCategory();
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteCategory();
        FillCategory();
    }
    protected void btnClearProduct_Click(object sender, EventArgs e)
    {
        clear();
    }

    protected void btnLoadCategory_Click(object sender, EventArgs e)
    {
        clear();
        FillCategory();
    }
    protected void GridViewCategory_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow r = GridViewCategory.SelectedRow;
        txtCategoryID.Text = r.Cells[1].Text;
        txtCategoryName.Text = r.Cells[2].Text;
        txtDescription.Text = r.Cells[3].Text;
    }


    protected void GridViewCategory_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        FillCategory();
        //GridViewCategory.PageIndex = e.NewPageIndex;
        GridViewCategory.DataBind();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SearchByName();
    }
}
